# Author: Mike Schladt - 2014
# Malware Intelligence Project 
# FrontEnd - Library for shared sql commands

import MySQLdb
import _mysql_exceptions

#Returns new active connection to the MIP database
def mip_connect() :
    #username and password stored in external files
    mysql_usr = open("/home/mike/.mysql_usr").read().strip()
    mysql_pwd = open("/home/mike/.mysql_pwd").read().strip()
    
    #connect to database and create cursor
    db = MySQLdb.connect("192.168.1.201", mysql_usr, mysql_pwd, "mip")
    
    return db
    
def get_stats() : 
    db = mip_connect()
    cursor = db.cursor()
    
    cmd = "SELECT COUNT(*) FROM samples"                                 
    cursor.execute(cmd)
    num_samples = cursor.fetchone()[0]

    cmd = "SELECT COUNT(*) FROM tags"                                 
    cursor.execute(cmd)
    num_tags = cursor.fetchone()[0]
    
    cmd = "SELECT COUNT(*) FROM sidtid"                                 
    cursor.execute(cmd)
    num_sidtid = cursor.fetchone()[0]
    
    stats = {
        'num_samples': "{:,}".format(num_samples),
        'num_tags' : "{:,}".format(num_tags),
        'num_sidtid' : "{:,}".format(num_sidtid),
    }
    
    cursor.close()
    db.close()
    
    return stats

#simple function to return 10 most recent samples
def get_most_recent_samples():
    cmd = "SELECT * FROM samples ORDER BY sid DESC LIMIT 10";
    db = mip_connect()
    cursor = db.cursor()
    cursor.execute(cmd)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return results       
    
    
#mip wrapper for the execute command    
#must provide cmd and data args
#MUST VALIDATE INPUT / NOT SECURE (SECURITY)
def execute(cmd, data) :     
    db = mip_connect()
    cursor = db.cursor()
    cursor.execute(cmd, data)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return results

#mip wrapper for MySQLdb.escape_string 
def escape_string(input_string) :      
    return MySQLdb.escape_string(input_string)
    
    
    
    
